| query [message #8813] |
Sat, 27 September 2003 08:02  |
Madhu
Messages: 36
Registered: April 2001
|
Member |
|
|
|
How can u retrieve second largest salary from emp table.Pleae give reply
|
|
|
|
|
|
|
|
| Re: simple way for second max [message #8830 is a reply to message #8818] |
Mon, 29 September 2003 08:14  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ramana,
Your query may look simple, but it is NOT the most efficient way of finding the 2nd max salary.
Your query :
-------------
SQL> select max(sal) from emp where sal <> ( select max(sal) from emp);
MAX(SAL)
----------
31059
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 SORT (AGGREGATE)
5 4 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- AS you see, your query is doing FULL table scan of EMP TWICE .
Now, the query that uses RANK() function :
--------------------------------------------
SQL> select sal from ( select sal,rank() over (order by sal desc) r from emp ) emp where r=2;
SAL
----------
31059
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT PUSHED RANK)
3 2 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- It goes for only ONE Full table scan of Emp.
So , a SIMPLE Query may not always be 'SIMPLE' !
Hope this helps
Thiru
|
|
|
|
| Re: simple way for second max [message #8841 is a reply to message #8830] |
Mon, 29 September 2003 22:26 |
ramana
Messages: 51
Registered: December 2000
|
Member |
|
|
hi Thiru,
thanx a lot for ur explanation.
really i am able to c the difference.
what i mean by simple is , its easy to write and
easy to understand for a biginer.
i am good at wrighting queries, but not in the
optimal way. i don't know how to use & understand
the explain plan. i have to concentrate on that.
plz suggest some books or web sites where i can get
a complete information abt the this.
thanx a lot
ramana
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
